#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : hello 
@file : transfer_oracle_data.py
@Author : sheen
@time : 2025/5/16 10:37
@func : 将siebel的数据同步到adm的临时表,做统计使用。
"""

import oracledb
from datetime import datetime
import time
import logging

from com.cn.for_cdc.common.cdc_conmons import oracle_siebel_connections, oracle_adm_connections
from com.cn.for_cdc.common.log_helper import configure_logging

configure_logging()


def transfer_oracle_data(src_cfg,tar_cfg):
    logging.info(f"Oracle Siebel 数据库配置:{src_cfg}")
    a_user = src_cfg['user']
    a_pwd = src_cfg['password']
    a_dsn = src_cfg['dsn']
    logging.info(f"Oracle ADM 数据库配置:{tar_cfg}")
    b_user = tar_cfg['user']
    b_pwd = tar_cfg['password']
    b_dsn = tar_cfg['dsn']


    sql38="""
SELECT
	/*+ parallel(16) */	DISTINCT ox.ROW_ID
FROM
	siebel.S_ORDER o,
	siebel.S_ORDER_XM ox,
	siebel.CX_PROMO_REF cpr
WHERE
	o.BU_ID = '1-7V7X'
	AND o.ROW_ID = ox.PAR_ROW_ID
	AND ox."TYPE" = 'Promotion Hit'
	AND ox.ATTRIB_34 = cpr.PROMO_NUM
	AND ox.last_upd < sysdate - 1

    
    """


    sql54="""
        SELECT /*+ parallel(8) */ DISTINCT soix.ROW_ID, S.order_num
        FROM siebel.S_ORDER S,
             SIEBEL.S_ORDER_ITEM SOI,
             SIEBEL.S_ORDER_ITEM_XM soix,
             DATAIN_USER.TMP_ICIBE_ORDER_TXN_MAY12 otm
        WHERE S.ROW_ID = SOI.ORDER_ID 
          AND SOI.ROW_ID = soix.PAR_ROW_ID 
          AND S.row_id = otm.ORDER_ID 
          AND S.status_cd = 'Submitted'
    """



    # SQL查询
    src_query = sql38
    logging.info(f"执行的SQL查询: {src_query}")

    # 目标表名
    tar_table = "TMP_ICIBE_ROW_ID"

    try:
        # 创建连接池
        a_pool = oracledb.create_pool(user=a_user, password=a_pwd,dsn=a_dsn,min=1,max=2,increment=1)
        b_pool = oracledb.create_pool(user=b_user, password=b_pwd, dsn=b_dsn,min=1, max=2,increment=1)

        # 获取连接
        a_conn = a_pool.acquire()
        b_conn = b_pool.acquire()

        # 准备目标表
        # with b_conn.cursor() as cursor:
        # # 检查并创建表(如果不存在)
        # cursor.execute(f"""
        #     BEGIN
        #         EXECUTE IMMEDIATE 'DROP TABLE {target_table} PURGE';
        #     EXCEPTION
        #         WHEN OTHERS THEN
        #             IF SQLCODE != -942 THEN
        #                 RAISE;
        #             END IF;
        #     END;
        # """)
        #
        # cursor.execute(f"""
        #     CREATE TABLE {target_table} (
        #         ROW_ID VARCHAR2(15),
        #         ORDER_NUM VARCHAR2(50),
        #         LOAD_DATE DATE,
        #         SOURCE VARCHAR2(20)
        #     ) NOLOGGING
        # """)
        # logging.info(f"已在Oracle B中创建表 {target_table}")

        # 执行查询并传输数据
        logging.info("开始数据传输...")
        start_time = time.time()
        batch_size = 5000  # 每批5000条
        total_rows = 0

        with a_conn.cursor() as a_cursor, b_conn.cursor() as b_cursor:
            # 设置数组大小提高fetch性能
            a_cursor.arraysize = batch_size

            # 执行查询
            a_cursor.execute(src_query)

            # 准备插入语句


            insert_adm_sql38 = f"""
                INSERT /*+ APPEND_VALUES */ INTO CRM_CDC_PP.{tar_table} 
                (ROW_ID)
                VALUES (:1)
            """

            insert_sql54 = f"""
                INSERT /*+ APPEND_VALUES */ INTO CRM_CDC_PP.{tar_table} 
                (ROW_ID, ORDER_NUM)
                VALUES (:1, :2)
            """
            insert_sql=insert_adm_sql38
            # 批量处理
            while True:
                rows = a_cursor.fetchmany(batch_size)
                if not rows:
                    break

                # 添加元数据
                load_date = datetime.now()
                rows_with_meta = [(row[0], row[1]) for row in rows]

                # 批量插入
                b_cursor.executemany(insert_sql, rows_with_meta)
                b_conn.commit()

                total_rows += len(rows)
                elapsed = time.time() - start_time
                speed = total_rows / elapsed if elapsed > 0 else 0

                logging.info(f"已传输: {total_rows:,} 行 | " f"速度: {speed:,.0f} 行/秒 | " f"耗时: {elapsed:.1f}秒")

        # 最终统计
        elapsed = time.time() - start_time
        logging.info(f"传输完成! 共传输 {total_rows:,} 行")
        logging.info(f"平均速度: {total_rows / elapsed:,.0f} 行/秒")
        logging.info(f"总耗时: {elapsed:.2f} 秒")

        # 验证数据
        with b_conn.cursor() as cursor:
            cursor.execute(f"SELECT COUNT(*) FROM {tar_table}")
            count = cursor.fetchone()[0]
            logging.info(f"目标表 {tar_table} 记录数验证: {count:,}")

        return tar_table, total_rows

    except oracledb.DatabaseError as e:
        error, = e.args
        logging.info(f"Oracle错误 {error.code}: {error.message}")
        if 'b_conn' in locals():
            b_conn.rollback()
        raise
    finally:
        # 释放资源
        if 'a_conn' in locals():
            a_pool.release(a_conn)
        if 'b_conn' in locals():
            b_pool.release(b_conn)
        if 'a_pool' in locals():
            a_pool.close()
        if 'b_pool' in locals():
            b_pool.close()


if __name__ == "__main__":
    try:
        src_cfg = oracle_siebel_connections['icibe_pro']
        tar_cfg = oracle_adm_connections['icibe_pro']
        target_table, row_count = transfer_oracle_data(src_cfg,tar_cfg)
        logging.info(f"数据已成功保存到ADM的 {target_table} 表")
    except Exception as e:
        logging.info(f"程序执行出错: {str(e)}")
